using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Model;
using System.Data.SqlClient;
namespace DAL
{
    public class MenuService
    {
        public MenuService()
        {
        }
        public static LMenu GetMenuById(int id, int language)
        {
            string sql = @"SELECT tbMenu.Id, LMenu" + language.ToString() + @".Name, LMenu" + language.ToString() + @".Unit, LMenu" + language.ToString() + @".Trait, LMenu" + language.ToString() + @".Code, 
                                  tblMenuItem.Price, tblItem_Type.MenuTypeId
                            FROM LMenu" + language.ToString() + @" RIGHT OUTER JOIN
                                  tblItem_Type INNER JOIN
                                  tblMenuItem INNER JOIN
                                  LMenu0 AS tbMenu ON tblMenuItem.Id = tbMenu.Id ON 
                                  tblItem_Type.MenuItemId = tbMenu.Id ON LMenu" + language.ToString() + @".Id = tbMenu.Id
                            WHERE (tbMenu.Id = " + id.ToString() + ")";
//            string sql = "SELECT LMenu" + language.ToString() + @".*, tblMenuItem.Price, tblItem_Type.MenuTypeId
//                            FROM LMenu" + language.ToString() + @" LEFT OUTER JOIN
//                                  tblMenuItem ON LMenu" + language.ToString() + @".Id = tblMenuItem.Id LEFT OUTER JOIN
//                                  tblItem_Type ON LMenu" + language.ToString() + @".Id = tblItem_Type.MenuItemId
//                            WHERE (LMenu" + language.ToString() + ".Id = " + id.ToString() + ") ";
            LMenu menu =null;
            using (SqlDataReader sdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, sql))
            {
                if (sdr.Read())
                {
                    menu = new LMenu(id);
                    menu.Language = language;
                    menu.Name = sdr["name"] is DBNull ? "" : sdr["name"].ToString();
                    menu.Price = sdr["price"] is DBNull ? 0 : decimal.Parse(sdr["price"].ToString());
                    menu.Trait = sdr["trait"] is DBNull ? "" : sdr["trait"].ToString();
                    menu.Unit = sdr["unit"] is DBNull ? "" : sdr["unit"].ToString();
                    menu.Code = sdr["Code"] is DBNull ? "" : sdr["Code"].ToString();
                    menu.Type = sdr["MenuTypeId"] is DBNull ? 0 : Int32.Parse(sdr["MenuTypeId"].ToString());
                }
            }
            return menu;
        }

        public static IList<LMenu> GetMenusByType(int type, int language)
        {
            IList<LMenu> menus = new List<LMenu>();
            string sql = "SELECT tbMenu.Id, LMenu" + language.ToString() + @".Name, LMenu" + language.ToString() + @".Unit, LMenu" + language.ToString() + @".Trait, LMenu" + language.ToString() + @".Code, 
                                  tblMenuItem.Price, tblItem_Type.MenuTypeId
                            FROM LMenu" + language.ToString() + @" RIGHT OUTER JOIN
                                  tblItem_Type INNER JOIN
                                  tblMenuItem INNER JOIN
                                  LMenu0 AS tbMenu ON tblMenuItem.Id = tbMenu.Id ON 
                                  tblItem_Type.MenuItemId = tbMenu.Id ON LMenu" + language.ToString() + @".Id = tbMenu.Id
                            WHERE (tblItem_Type.MenuTypeId = " + type.ToString() + ") ";
            using (SqlDataReader sdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, sql))
            {
                while (sdr.Read())
                {
                    LMenu menu = new LMenu((int)sdr["id"]);

                    menu.Name = sdr["name"] is DBNull ? "" : sdr["name"].ToString();
                    menu.Price = sdr["price"] is DBNull ? 0 : decimal.Parse(sdr["price"].ToString());
                    menu.Trait = sdr["trait"] is DBNull ? "" : sdr["trait"].ToString();
                    menu.Unit = sdr["unit"] is DBNull ? "" : sdr["unit"].ToString();
                    menu.Code = sdr["Code"] is DBNull ? "" : sdr["Code"].ToString();
                    menu.Type = sdr["MenuTypeId"] is DBNull ? 0 : Int32.Parse(sdr["MenuTypeId"].ToString());
                    menu.Language = language;
                    menus.Add(menu);
                }
            }
            return menus;
        }
        public static IList<LMenu> GetMenus(int language)
        {
            IList<LMenu> menus=new List<LMenu>();
            string sql = "SELECT tbMenu.Id, LMenu" + language.ToString() + @".Name, LMenu" + language.ToString() + @".Unit, LMenu" + language.ToString() + @".Trait, LMenu" + language.ToString() + @".Code, 
                                  tblMenuItem.Price, tblItem_Type.MenuTypeId
                            FROM LMenu" + language.ToString() + @" RIGHT OUTER JOIN
                                  tblItem_Type INNER JOIN
                                  tblMenuItem INNER JOIN
                                  LMenu0 AS tbMenu ON tblMenuItem.Id = tbMenu.Id ON 
                                  tblItem_Type.MenuItemId = tbMenu.Id ON LMenu" + language.ToString() + @".Id = tbMenu.Id";
            using (SqlDataReader sdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, sql))
            {
                while (sdr.Read())
                {
                    LMenu menu = new LMenu((int)sdr["id"]);
                    menu.Language = language;
                    menu.Name = sdr["name"] is DBNull ?  "":sdr["name"].ToString();
                    menu.Price = sdr["price"] is DBNull ?  0:decimal.Parse(sdr["price"].ToString()) ;
                    menu.Trait = sdr["trait"] is DBNull ? "":sdr["trait"].ToString() ;
                    menu.Unit = sdr["unit"] is DBNull ? "":sdr["unit"].ToString() ;
                    menu.Code = sdr["Code"] is DBNull ?"": sdr["Code"].ToString();
                    menu.Type = sdr["MenuTypeId"] is DBNull ? 0:Int32.Parse(sdr["MenuTypeId"].ToString());
                    menus.Add(menu);
                }
            }
            return menus;
        }
        public static int Add(LMenu menu)
        {
            int newMenu = GetNewId();
            string sql = "select Id from tblLanguage order by id";
            StringBuilder sqlAdd = new StringBuilder();
            using (SqlDataReader sdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, sql))
            {
                while (sdr.Read())
                {
                    sqlAdd.Append(" insert into LMenu" + sdr[0].ToString() + " (id,name, unit, trait ,code) VALUES(" + newMenu.ToString() + ",'" + menu.Name + "','" + menu.Unit + "','" + menu.Trait + "','" + menu.Code + "') ");
                }
                sqlAdd.Append(" insert into tblItem_Type (MenuItemId,MenuTypeId) VALUES(" + newMenu.ToString() + "," + menu.Type.ToString() + ") ");
                sqlAdd.Append(" insert into tblMenuItem (id,price) VALUES(" + newMenu.ToString() + "," + menu.Price.ToString() + ") ");
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, "StoredTransaction", new SqlParameter("@Sql", sqlAdd.ToString()));
            }
            return newMenu;
        }
        public static void Edit(LMenu menu)
        {
            string sql = @"update LMenu" + menu.Language.ToString() + " set name='" + menu.Name + "',Code='" + menu.Code + "',Unit='" + menu.Unit + "',Trait='" + menu.Trait + "' where id=" + menu.Id.ToString()
                + " update tblItem_Type set MenuTypeId=" + menu.Type.ToString() + " where MenuItemId=" + menu.Id.ToString()
                + " update tblMenuItem set price=" + menu.Price.ToString() + " where Id=" + menu.Id.ToString();
            SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, "StoredTransaction", new SqlParameter("@Sql", sql));
        }
        public static void Delete(LMenu menu)
        {
            string sql = "select Id from tblLanguage order by id";
            StringBuilder sqlDel = new StringBuilder();
            using (SqlDataReader sdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, sql))
            {
                while (sdr.Read())
                {
                    sqlDel.Append(" delete LMenu" + menu.Language.ToString() + "  where id=" + menu.Id.ToString());
                }
                sqlDel.Append(" delete tblItem_Type  where MenuTypeId=" + menu.Id.ToString());
                sqlDel.Append(" delete tblMenuItem  where Id=" + menu.Id.ToString());
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, "StoredTransaction", new SqlParameter("@Sql", sqlDel.ToString()));
            }
        }
        private static int GetNewId()
        {
            string sql = "select top 1 id from LMenu0 order by id desc ";
            int i = (int)SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, CommandType.Text, sql);
            return i + 1;
        }
    }
}
